package vip.meeet.mysql.jpa.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
import vip.meeet.mysql.jpa.entity.User;

import java.util.List;

public interface UserJpaRepository extends JpaRepository<User,Integer> {

    @Query(value = "select * from user where username like ?1",nativeQuery = true)
    List<User> queryByUsername(String username);

    @Query(value = "select * from user where username = ?1", nativeQuery = true)
    User queryByUsernameFirst(String username);

    @Query("select id,username from User where username like %?1%")
    List<User> getByUsernameLike(String username);

    @Query("select id,username from User where id = :id")
    User getById(@Param("id") String userId);

    @Query("select id,username from User where username = :username or email = :email")
    User getByUsernameOrEmail(@Param("username") String username, @Param("email") String email);

    @Query("update User set username=?1 where id=?2")
    @Modifying
    void updateUserByNameId(String username,Integer id);

    @Transactional()
    @Modifying
    @Query("update User set username = ?1 where id = ?2")
    int updateUsernameById(Integer id, String username);

    @Transactional()
    @Modifying
    @Query("delete from User where username = ?1")
    void deleteByUsername(String username);

    @Query("select u from User u where u.username like ?1%")
    Page<User> findByUsernameLikePageable(String username, Pageable pageable);

    @Query("select id,username from User where username like %?1%")
    List<User> findByUsernameAndSort(String username, Sort sort);

    @Query(value = "select * from user where username like %?1%",
            countQuery = "select count(1) from user where username = %?1%",
            nativeQuery = true)
    Page<User> queryByUsernameLike(String username, Pageable pageable);
}
